package com.kyivmohyla.kma2.dao;

import com.kyivmohyla.kma2.dao.mapper.OrderRowMapper;
import com.kyivmohyla.kma2.model.Order;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OrderDaoJdbc extends JdbcDaoSupport implements OrderDao {

    @Override
    public Order getById(int id) throws DataAccessException {
        return getJdbcTemplate()
                .queryForObject(
                        "SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id INNER JOIN staff st ON st.staff_id = orders.engineer_id WHERE orders.order_id = ?",
                        new Object[]{id}, new OrderRowMapper());
    }

    @Override
    public List<Order> getAll() throws DataAccessException {
        return getJdbcTemplate()
                .query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id INNER JOIN staff st ON st.staff_id = orders.engineer_id ORDER BY orders.order_id",
                        new OrderRowMapper());
    }

    @Override
    public void create(Order order) throws DataAccessException {
        getJdbcTemplate()
                .update("INSERT INTO orders (orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, orders.customer_id, orders.engineer_id) VALUES (order_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        order.getCreateDate(),
                        order.getStartDate(),
                        order.getEndDate(),
                        order.getDesStartDate(),
                        order.getDesEndDate(),
                        order.getCommentary(), order.getStateId(),
                        order.getCustomerId(),
                        (order.getEngineerId() == 0) ? null : order.getEngineerId());
    }

    @Override
    public void update(Order order) throws DataAccessException {
        getJdbcTemplate()
                .update("UPDATE orders SET orders.create_date = ?, orders.start_date = ?, orders.end_date = ?, orders.des_start_date = ?, orders.des_end_date = ?, orders.commentary = ?, orders.state_id = ?, orders.customer_id = ?, orders.engineer_id = ? WHERE orders.order_id = ?",
                        order.getCreateDate(),
                        order.getStartDate(),
                        order.getEndDate(),
                        order.getDesStartDate(),
                        order.getDesEndDate(),
                        order.getCommentary(), order.getStateId(),
                        order.getCustomerId(),
                        (order.getEngineerId() == 0) ? null : order.getEngineerId(), order.getOrderId());
    }

    @Override
    public void delete(Order order) throws DataAccessException {
        getJdbcTemplate().update("DELETE FROM orders WHERE orders.order_id = ?",
                order.getOrderId());
    }

    @Override
    public List<Order> getOrdersByState(int[] states) throws DataAccessException {
        List<Order> orders = new ArrayList<Order>();
        if (states.length > 0) {
            String stateCondition = "(";
            String partStart = "(orders.state_id = '";
            String partEnd = "')";
            String or = "OR";
            String queryEnd = ")";
            for (int i = 0; i < states.length - 1; i++) {
                stateCondition += partStart + states[i] + partEnd + " " + or + " ";
            }
            stateCondition += partStart + states[states.length - 1] + partEnd + queryEnd;
            orders = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id WHERE " + stateCondition + " ORDER BY orders.order_id", new OrderRowMapper());
        }
        return orders;
    }

    @Override
    public List<Order> getOrdersByStateAndDate(int[] states, String date) throws DataAccessException {
        List<Order> orders = new ArrayList<Order>();
        if (states.length > 0 && !date.equals("")) {
            String stateCondition = "(";
            String partStart = "(orders.state_id = '";
            String partEnd = "')";
            String or = "OR";
            String queryEnd = ")";
            for (int i = 0; i < states.length - 1; i++) {
                stateCondition += partStart + states[i] + partEnd + " " + or + " ";
            }
            stateCondition += partStart + states[states.length - 1] + partEnd + queryEnd;
            String dateCondition = "(TO_CHAR(orders.des_start_date,'DD.MM.YYYY')='" + date + "')";
            String condition = stateCondition + " AND " + dateCondition;
            List<Order> withoutEngineer = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id WHERE " + condition + " ORDER BY orders.order_id", new OrderRowMapper());
            List<Order> withEngineer = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id INNER JOIN staff st ON st.staff_id = orders.engineer_id WHERE " + condition + " ORDER BY orders.order_id", new OrderRowMapper());
            orders.addAll(withoutEngineer);
            orders.removeAll(withEngineer);
            orders.addAll(withEngineer);
        }
        return orders;
    }

    @Override
    public List<Order> getOrdersByStateAndDateAndPosition(int[] states, String date, int position) throws DataAccessException {
        List<Order> orders = new ArrayList<Order>();
        if (states.length > 0 && !date.equals("")) {
            String stateCondition = "(";
            String partStart = "(orders.state_id = '";
            String partEnd = "')";
            String or = "OR";
            String queryEnd = ")";
            for (int i = 0; i < states.length - 1; i++) {
                stateCondition += partStart + states[i] + partEnd + " " + or + " ";
            }
            stateCondition += partStart + states[states.length - 1] + partEnd + queryEnd;
            String dateCondition = "(TO_CHAR(orders.start_date,'DD.MM.YYYY')='" + date + "')";
            String positionCondition = "(orders.engineer_id='" + position + "')";
            String condition = stateCondition + " AND " + dateCondition + " AND " + positionCondition;
            orders = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id INNER JOIN staff st ON st.staff_id = orders.engineer_id WHERE " + condition + " ORDER BY orders.order_id", new OrderRowMapper());
        }
        return orders;
    }

    @Override
    public Order getOrderWithMaxId() throws DataAccessException {
        return getJdbcTemplate().queryForObject("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id WHERE orders.order_id =(SELECT MAX(orders.order_id) FROM orders)", new OrderRowMapper());
    }

    public List<Order> searchIntersection(int engineerId, String date, String startTime, String endTime) throws DataAccessException {
        List<Order> orders;
        orders = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date,orders.des_start_date, orders.des_end_date, orders.commentary,orders.state_id, s.name as state_name,CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name,c.address as customer_address,CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name,orders.customer_id, orders.engineer_id" +
                " FROM orders" +
                " INNER JOIN STATE s ON s.state_id = orders.state_id" +
                " INNER JOIN customer c ON c.customer_id = orders.customer_id" +
                " INNER JOIN staff st ON st.staff_id = orders.engineer_id" +
                " WHERE orders.engineer_id = " + engineerId + " " +
                "AND to_char(orders.start_date,'DD.MM.YYYY') = '" + date + "' " +
                "AND ((to_date('" + startTime + "','HH24:MI') < to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI') AND to_date('" + endTime + "','HH24:MI') > to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI')) OR " +
                "(to_date('" + startTime + "','HH24:MI') = to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI') AND to_date('" + endTime + "','HH24:MI') = to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI')) OR" +
                "((to_date('" + startTime + "','HH24:MI') < to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI') AND to_date('" + startTime + "','HH24:MI') > to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI')) OR" +
                "(to_date('" + endTime + "','HH24:MI') > to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI') AND to_date('" + endTime + "','HH24:MI') < to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI'))))", new OrderRowMapper());

        return orders;
    }
//    public List<Order> searchIntersection(int engineerId, String date, String startTime, String endTime) throws DataAccessException {
//        List<Order> orders;
//        orders = getJdbcTemplate().query("SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date,orders.des_start_date, orders.des_end_date, orders.commentary,orders.state_id, s.name as state_name,CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name,c.address as customer_address,CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name,orders.customer_id, orders.engineer_id" +
//                " FROM orders" +
//                " INNER JOIN STATE s ON s.state_id = orders.state_id" +
//                " INNER JOIN customer c ON c.customer_id = orders.customer_id" +
//                " INNER JOIN staff st ON st.staff_id = orders.engineer_id" +
//                " WHERE orders.engineer_id = " + engineerId + " " +
//                "AND to_char(orders.start_date,'DD.MM.YYYY') = '" + date + "' " +
//                "AND ( (to_date('" + startTime + "','HH24:MI') < to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI') AND to_date('" + endTime + "','HH24:MI') > to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI')) OR " +
//                "((to_date('" + startTime + "','HH24:MI') < to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI') AND to_date('" + startTime + "','HH24:MI') > to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI')) OR" +
//                "(to_date('" + endTime + "','HH24:MI') > to_date(to_char(orders.start_date,'HH24:MI'),'HH24:MI') AND to_date('" + endTime + "','HH24:MI') < to_date(to_char(orders.end_date,'HH24:MI'),'HH24:MI'))))", new OrderRowMapper());
//
//        return orders;
//    }

    @Override
    public Map<Integer, Boolean> validateOrderSchedule(List<Order> orders) throws DataAccessException {
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
        //true means that there is no intersection
        //false means that there is some intersection
        Map<Integer, Boolean> orderStatus = new HashMap<Integer, Boolean>();
        //taking all orders
        List<Order> orderList = new ArrayList<Order>();
        for (Order order : orders) {
            orderList.add(getJdbcTemplate()
                    .queryForObject(
                            "SELECT orders.order_id, orders.create_date, orders.start_date, orders.end_date, orders.des_start_date, orders.des_end_date, orders.commentary, orders.state_id, s.name as state_name, CONCAT(CONCAT(c.first_name,' '),c.last_name) as customer_name, c.address as customer_address, CONCAT(CONCAT(st.first_name,' '),st.last_name) as engineer_name, orders.customer_id, orders.engineer_id FROM orders INNER JOIN STATE s ON s.state_id = orders.state_id INNER JOIN customer c ON c.customer_id = orders.customer_id INNER JOIN staff st ON st.staff_id = orders.engineer_id WHERE orders.order_id = ?",
                            new Object[]{order.getOrderId()}, new OrderRowMapper()));
        }
        for (Order order : orderList) {
            //at the beginning we assume that everything is ok,
            //if we find intersection, we'll change the value to false;
            orderStatus.put(order.getOrderId(), true);
        }
        for (Order order : orderList) {
            List<Order> ordersForEngineer = getOrdersByStateAndDateAndPosition(new int[]{2, 3}, dateFormat.format(order.getStartDate()), order.getEngineerId());
            if (ordersForEngineer.size() > 1) {
                for (Order order1 : ordersForEngineer) {
                    String startTime = timeFormat.format(order1.getStartDate());
                    String endTime = timeFormat.format(order1.getEndDate());
                    String orderDate = dateFormat.format(order1.getStartDate());
                    List<Order> intersectedOrders = searchIntersection(order1.getEngineerId(), orderDate, startTime, endTime);
                    if (intersectedOrders.size() > 1) {
                        orderStatus.put(order1.getOrderId(), false);
                    }
                }
            } else {
                orderStatus.put(ordersForEngineer.get(0).getOrderId(), true);
            }
        }
        return orderStatus;
    }

    @Override
    public void updateList(List<Order> orders) throws DataAccessException {
        for (Order order : orders) {
            update(order);
        }
    }
}
